import datetime
import os
import shutil

import openpyxl
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter

from function_v1 import *
from tools_pmc import (fill_blue, fill_yellow,
                       table_array_wlxxb,
                       today_str1, yesterday_str1,
                       lj_mccr, alignment_wrap_text_F, detect_date, lj_change_ms, lj_msd_md,
                       lj_mccr_yesterday, lj_JH0020_1, erp_fc_2, erp_fc_1
                       )

try:
    wb1 = openpyxl.load_workbook(
        r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\自定义物料参数" + today_str1 + ".xlsx")
    lj_wb1 = r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\自定义物料参数" + today_str1 + ".xlsx"
except FileNotFoundError:
    wb1 = openpyxl.load_workbook(
        r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\自定义物料参数" + yesterday_str1 + ".xlsx")
    lj_wb1 = r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\自定义物料参数" + yesterday_str1 + ".xlsx"
ws1 = wb1.worksheets[0]

wb_mccr_today = openpyxl.load_workbook(lj_JH0020_1)
ws_mccr_today = wb_mccr_today.worksheets[0]  # 今日自定义物料参数表

wb_mccr_yestoday = openpyxl.load_workbook(lj_mccr_yesterday)
ws_mccr_yestoday = wb_mccr_yestoday.worksheets[0]

wb_msd_md = openpyxl.load_workbook(lj_msd_md)
ws_msd_md = wb_msd_md.worksheets[0]
ws_msd_md_1 = wb_msd_md.worksheets[1]

wb_change_ms = openpyxl.load_workbook(lj_change_ms)  # 物料状态变更台账
ws_change_ms = wb_change_ms.worksheets[0]
ws_change_ms_temp1 = wb_change_ms.worksheets[1]
ws_change_ms_temp2 = wb_change_ms.worksheets[2]

list_change = change()  # 待处理变更

ws_mccr_today.insert_cols(4, 2)
ws_mccr_today.insert_cols(13, 1)
# 原状态保存
for i in range(1, ws1.max_row + 1):
    for j in list_change:  # 导入其他变更信息
        if j[3] == ws_mccr_today.cell(i, 1).value:
            ws1.cell(i, 4).value = j[5]
    ws_change_ms_temp2.cell(i, 1).value = ws1.cell(i, 1).value
    ws_change_ms_temp2.cell(i, 2).value = ws1.cell(i, 4).value
wb1.save(lj_wb1)
# 修补自定义参数表数据
for i in range(2, ws_mccr_today.max_row + 1):
    if int(ws_mccr_today.cell(i, 1).value[0:5]) in [10309, 10310,  # 线缆号段 10310待处理
                                                    10202,  # 镜头号段 10202待处理
                                                    10206, 10207,  # 光源号段
                                                    10314,  # 网卡号段
                                                    10804,  # 非原材料杂项
                                                    10805  # 外购采集卡号段
                                                    ]:
        ws_mccr_today.cell(i, 1).fill = fill_yellow
    for j in range(2, ws_change_ms_temp2.max_row + 1):
        if ws_mccr_today.cell(i, 1).value == ws_change_ms_temp2.cell(j, 1).value:
            ws_mccr_today.cell(i, 4).value = ws_change_ms_temp2.cell(j, 2).value
            break
    temp = vlookup_1(ws_mccr_today.cell(i, 1).value, table_array_wlxxb)
    if temp is None:
        continue
    ws_mccr_today.cell(i, 6).value = temp[0]  # 写入可用量
    # 导入 昨日物料状态信息
    for j in range(1, 3, 1):
        erp_fc_2(ws_mccr_today.cell(i, j + 3))
    # 写入计划请购量、非计划请购量、入库数量
    ws_mccr_today.cell(i, 13).value = temp[1]
    erp_fc_2(ws_mccr_today.cell(i, 13))

    # 日期标准化
    if len(ws_mccr_today.cell(i, 16).value) == 6:
        temp = "20" + ws_mccr_today.cell(i, 16).value
    else:
        temp = ws_mccr_today.cell(i, 16).value.replace('建号', '')
    ws_mccr_today.cell(i, 16).value = detect_date(temp, 1)  # 统一日期格式
    ws_mccr_today.cell(i, 16).number_format = 'yyyy/mm/dd'
    for j in [36, 42]:
        if ws_mccr_today.cell(i, j).value is not None:
            ws_mccr_today.cell(i, j).value = detect_date(ws_mccr_today.cell(i, j).value, 1)  # 统一日期格式
            ws_mccr_today.cell(i, j).number_format = 'yyyy/mm/dd'

# 表头处理
list1 = ["物料状态", "通知", "可用数量"]
for i in range(0, 3, 1):
    ws_mccr_today.cell(1, 4 + i).value = list1[i]
    erp_fc_1(ws_mccr_today.cell(1, 4 + i))

ws_mccr_today.cell(1, 13).value = "进货数量"
erp_fc_1(ws_mccr_today.cell(1, 13))


list1 = [2, 3, 39, 40, 41]
for i in range(2, ws_mccr_today.max_row + 1):
    for j in list1:
        ws_mccr_today.cell(i, j).alignment = alignment_wrap_text_F

list1 = ([11, 30, 30, 10]
         + [8 for i in range(11)]
         + [11] + [8 for i in range(19)]
         + [11, 8, 8, 11, 20, 20, 11, 8, 15,8])
for i in range(0, len(list1)):
    ws_mccr_today.cell(1, i + 1).fill = fill_blue
    ws_mccr_today.column_dimensions[get_column_letter(i + 1)].width = list1[i]

ws_mccr_today.title = "自定义物料参数" + today_str1
ws_mccr_today.freeze_panes = 'A2'  # 冻结窗口
range_active = "A1:AR" + str(ws_mccr_today.max_row)
ws_mccr_today.auto_filter.ref = range_active


# ---------------------------------------------冻结A状态物料参数监控---------------------------------------------------------
def qyd(sn, list_col: list):
    list_temp = []
    for i_y in range(2, ws_mccr_yestoday.max_row + 1):
        if ws_mccr_yestoday.cell(i_y, 1).value == sn:
            for m in list_col:
                list_temp.append(ws_mccr_yestoday.cell(i_y, m).value)
            break
    return list_temp


def pam_matrix(i_sn, list_title: list, list_cols: list, list_tav: list):
    str1 = ''
    for n in range(len(list_title)):
        if ws_mccr_today.cell(i_sn, list_cols[n]).value == list_tav[n]:
            str1 += list_title[n] + "=" + str(ws_mccr_today.cell(i_sn, list_cols[n]).value) + "\n"
        else:
            str1 += list_title[n] + "=" + str(
                ws_mccr_today.cell(i_sn, list_cols[n]).value) + "(标准值=" + str(list_tav[n]) + ")" + "\n"
    return str1


def pam(i_sn, saterial_status):
    list_temp1 = qyd(ws_mccr_today.cell(i_sn, 1).value, [10, 11, 12, 13, 6, 19, 21])
    str1 = "关键参数监控：\n"
    if saterial_status in ["未启用"]:
        str1 += pam_matrix(i_sn,
                           ["可用量", "计划请购量", "非计划请购量", "BOM成品引用次数"],
                           [6, 10, 11, 19],
                           [0, list_temp1[0], list_temp1[1], 0])

    elif saterial_status == "有呆滞风险":
        str1 += pam_matrix(i_sn,
                           ["计划请购量", "非计划请购量", "进货数量", "BOM成品引用次数"],
                           [10, 11, 13, 19],
                           [list_temp1[0], list_temp1[1], list_temp1[3], 0])
    elif saterial_status in ["未销货呆滞", "未启用呆滞"]:
        str1 += pam_matrix(i_sn,
                           ["可用量", "计划请购量", "非计划请购量", "BOM成品引用次数"],
                           [6, 10, 11, 19],
                           [list_temp1[4], list_temp1[0], list_temp1[1], 0])
    elif saterial_status in ["停用呆滞", "停用"]:
        str1 += pam_matrix(i_sn,
                           ["可用量", "计划请购量", "非计划请购量", "非原材料仓请购量", "进货数量",
                            "Y状态BOM成品引用次数"],
                           [6, 10, 11, 12, 13, 21],
                           [list_temp1[4], list_temp1[0], list_temp1[1], list_temp1[2], list_temp1[3], 0])

    elif saterial_status == "正常A":
        if ws_mccr_today.cell(i_sn, 21).value == 0:
            str1 += "Y状态BOM成品引用次数=0，标准值≠0\n"
        else:
            str1 += "Y状态BOM成品引用次数≠0\n"
        if ws_mccr_today.cell(i_sn, 30).value < 500:
            str1 += "365天生产使用量=" + str(ws_mccr_today.cell(i_sn, 30).value) + "标准值>500\n"
        else:
            str1 += "365天生产使用量>500"
        if ws_mccr_today.cell(i_sn, 35).value != 0:
            str1 += "在库超365天数量=" + str(ws_mccr_today.cell(i_sn, 35).value) + "标准值=0\n"
        else:
            str1 += "在库超365天数量=0\n"
    elif saterial_status == "正常B":
        if ws_mccr_today.cell(i_sn, 21).value == 0:
            str1 += "Y状态BOM成品引用次数=0，标准值≠0\n"
        else:
            str1 += "Y状态BOM成品引用次数≠0\n"
    return str1


a = ws_msd_md.max_row + 1
if ws_msd_md.cell(1, ws_msd_md.max_column).value.day == datetime.datetime.now().day:
    b = ws_msd_md.max_column
else:
    b = ws_msd_md.max_column + 1
    ws_msd_md.cell(1, b).value = datetime.datetime.now()
    ws_msd_md.cell(1, b).number_format = 'yyyy/mm/dd'
    ws_msd_md.column_dimensions[get_column_letter(b)].width = 17

c = ws_msd_md_1.max_row + 1
for i in range(2, ws_mccr_today.max_row + 1):
    temp = False
    for j in range(2, ws_msd_md.max_row + 1):
        if ws_mccr_today.cell(i, 1).value == ws_msd_md.cell(j, 1).value:
            temp = True
            if ws_mccr_today.cell(i, 4).value is not None:
                temp2 = ("当日状态：" + ws_mccr_today.cell(i, 4).value + "\n"
                         + pam(i, ws_mccr_today.cell(i, 4).value))
                ws_msd_md.cell(j, b).value = temp2
                if "标准" in temp2:
                    ws_msd_md.cell(j, b).fill = PatternFill(patternType="solid", fgColor="EF949F")
                    for x in range(1, 4):
                        ws_msd_md_1.cell(c, x).value = ws_msd_md.cell(j, x).value
                    ws_msd_md_1.cell(c, 4).value = datetime.datetime.now()
                    ws_msd_md_1.cell(c, 4).number_format = 'yyyy/mm/dd'
                    ws_msd_md_1.cell(c, 5).value = temp2
                    c += 1
                else:
                    ws_msd_md.cell(j, b).fill = PatternFill(patternType="solid", fgColor="6FC665")

            break
    if not temp:
        for j in range(1, 4):
            ws_msd_md.cell(a, j).value = ws_mccr_today.cell(i, j).value
        a += 1

changeDate_writeback(ws_change_ms, list_change)  # 状态变更台账记录

ws_mccr_today.titles = "自定义物料参数" + today_str1
wb_mccr_today.save(lj_mccr)
wb_msd_md.save(lj_msd_md)
wb_change_ms.save(lj_change_ms)

shutil.copy(lj_mccr, r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理")
try:
    os.remove(
        "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/自定义物料参数" + yesterday_str1 + ".xlsx")
except FileNotFoundError:
    print()
